
package com.scalar.chiptrack.operations.lotshipping.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.ProcessStep;
import com.scalar.chiptrack.utils.SqlQueryMappings;


public final class LotShippingDAO {

    /**
     * returns forward lot shipping flow for a given process Step ID (including this process step)
     */
    public static ArrayList getForwardLotShippingFlowByProcessStepId (String processStepId) throws ScalarException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try{
            conn = DataSource.getConnection();
            ps = conn.prepareStatement (SqlQueryMappings.get ("operations.lotshipping.getForwardLotShippingFlowByProcessStepId"));
            ps.setString (1, processStepId);
            rs = ps.executeQuery();
            
            while (rs.next()) {
                list.add (new ProcessStep (rs.getInt ("ProcessStep_ID"), rs.getString ("PS_Type"), rs.getString ("Process_Type_Name")));
            }
            
        }
        
        catch (SQLException se) {
            se.printStackTrace();
            throw new ScalarException ("Could not load Lot Shipping Flow for process StepId: " + processStepId, se);
        }
        
        finally{
            if (rs != null){
                try { rs.close(); } catch (SQLException igonore) { }
            }
            
            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }
            
            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }
    
    
    /**
     * Saves lot shipping instructions information into Lot_shipping_instructions Table
     */
    public static void saveLotShippingInstructions (String lotId, String dateIn, int nextProcessStepId, int nextLocationId, String processTimeType) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        boolean updateRow = false;
        
        try {
            conn = DataSource.getConnection();
            
            // STEP 1
            // CHECK IF THERE IS A ROW EXISTS FOR THIS LOT_ID
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.lotshipping.getLotShippingInstructionsByLotId"));
            pstmt.setString (1, lotId);
            rset = pstmt.executeQuery();
            if (rset.next()) {
                updateRow = true;   // row exists
            }
            
            if (rset != null) {
                rset.close();
                rset = null;
            }
            
            pstmt.close();
            pstmt = null;
            
            
            // STEP 2 
            // GET EXPECTED DATE
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.lotshipping.getExpectedDate"));
            pstmt.setString (1, lotId);
            pstmt.setString (2, dateIn);
            pstmt.setInt (3, nextProcessStepId);
            pstmt.setInt (4, nextLocationId);
            pstmt.setInt (5, (("EXPRESS".equalsIgnoreCase (processTimeType)) ? 0 : 1));
            
            Date expectedDate = null;
            
            rset = pstmt.executeQuery();
            if (rset.next()) {
                expectedDate = rset.getDate ("Expected_Date");
            }
            
            if (expectedDate == null) {
                throw new ScalarException ("Could not save lot shipping instructions,.. Expected_Date is null");
            }
            
            if (rset != null) {
                rset.close();
                rset = null;
            }
            
            pstmt.close();
            pstmt = null;
            
            // STEP 3
            // INSERT OR UPDATE LOT SHIPPING INSTRUCTIONS TABLE
            if (updateRow == true) {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.lotshipping.updateLotShippingInstructions"));

                pstmt.setInt (1, nextProcessStepId);
                pstmt.setInt (2, nextLocationId);
                pstmt.setDate (3, expectedDate);
                pstmt.setInt (4, (("EXPRESS".equalsIgnoreCase (processTimeType)) ? 0 : 1));
                pstmt.setString (5, "COMPLETED");
                pstmt.setString (6, lotId);
                
                pstmt.executeUpdate();
            }
            else {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.lotshipping.saveLotShippingInstructions"));

                pstmt.setString (1, lotId);
                pstmt.setInt (2, nextProcessStepId);
                pstmt.setInt (3, nextLocationId);
                pstmt.setDate (4, expectedDate);
                pstmt.setInt (5, (("EXPRESS".equalsIgnoreCase (processTimeType)) ? 0 : 1));
                pstmt.setString (6, "COMPLETED");
                
                pstmt.executeUpdate();
            }
            
            success = true;
        }
        
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Lot Shipping Instructions", sqle);
        }
        
        finally {
            try {
                if (conn != null) {
                    if (success == true) { conn.commit(); }
                    else { conn.rollback(); }
                }
            }
            catch (SQLException ignore) {}
            
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        
    }

}